/**
 * 
 */
package ca.fuguo.pos.swingui;

/**
 * @author Jeff
 *
 */
import java.sql.*;

public class Connect
{
    public static void main (String[] args)
    {
        Connection conn = null;

        try
        {
        	//create connection
            String userName = "testuser";
            String password = "testpass";
            String url = "jdbc:mysql://localhost/test";
            Class.forName ("com.mysql.jdbc.Driver").newInstance ();
            conn = DriverManager.getConnection (url, userName, password);
            System.out.println ("Database connection established");
            
            //create table
            Statement s = conn.createStatement ();
            int count;
            s.executeUpdate ("DROP TABLE IF EXISTS animal");
            s.executeUpdate (
                        "CREATE TABLE animal ("
                        + "id INT UNSIGNED NOT NULL AUTO_INCREMENT,"
                        + "PRIMARY KEY (id),"
                        + "name CHAR(40), category CHAR(40))");
            count = s.executeUpdate (
                        "INSERT INTO animal (name, category)"
                        + " VALUES"
                        + "('snake', 'reptile'),"
                        + "('frog', 'amphibian'),"
                        + "('tuna', 'fish'),"
                        + "('racoon', 'mammal')");
            s.close ();
            System.out.println (count + " rows were inserted");
            
            //select statement
            Statement statement = conn.createStatement ();
            statement.executeQuery ("SELECT id, name, category FROM animal");
            ResultSet rs = statement.getResultSet ();
            //rs.get();
            int count1 = 0;
            while (rs.next ())
            {
                int idVal = rs.getInt ("id");
                String nameVal = rs.getString ("name");
                String catVal = rs.getString ("category");
                System.out.println (
                        "id = " + idVal
                        + ", name = " + nameVal
                        + ", category = " + catVal);
                ++count1;
            }
            //rs.close ();
            //statement.close ();
            System.out.println (count1 + " rows were retrieved");
            
            
            
//            //another
//            //ResultSet rs = statement.getResultSet ();
//            int idVal = rs.getInt (1);
//            //String nameVal = rs.getString (2);
//            String catVal = rs.getString (3);
//            String nameVal = rs.getString ("name");
//            if (rs.wasNull ())
//                nameVal = "(no name available)";
//            
//            //another
//            System.out.println("Here");
//            PreparedStatement sss;
//            sss = conn.prepareStatement (
//                        "INSERT INTO animal (name, category) VALUES(?,?)");
//            sss.setString (1, nameVal);
//            sss.setString (2, catVal);
//            int count2 = sss.executeUpdate ();
//            sss.close ();
//            System.out.println (count2 + " rows were inserted");
//            //rs.close ();
        }
        catch (Exception e)
        {
            System.err.println ("Cannot connect to database server" + e);
        }
        finally
        {
            if (conn != null)
            {
                try
                {
                    conn.close ();
                    System.out.println ("Database connection terminated");
                }
                catch (Exception e) { /* ignore close errors */ }
            }
        }
    }
}

